package com.ihtry.dao;

import com.ihtry.entity.Book;
import com.ihtry.util.JdbcTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {

    List<Book> books = new ArrayList<Book>();

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    /**
     * 查询全部图书
     *
     * @return 图书列表
     */
    public List<Book> getBooks() {
        JdbcTool jdbcTool = new JdbcTool();
        String sql = "select * from books";

        try {
            conn = jdbcTool.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer id = rs.getInt("id");
                String name = rs.getString("name");
                String author = rs.getString("author");
                String express = rs.getString("express");
                double money = rs.getDouble("money");
                String img = rs.getString("img");
                Book book = new Book(id, name, author, money, express, img);
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcTool.release(conn, ps, rs);
        }

        return books;
    }

    /**
     * 添加图书
     *
     * @param bookname 书名
     * @param author 作者
     * @param money 价格
     * @param express 出版社
     * @param img 图片
     */
    public void add(String bookname, String author, Double money, String express, String img) {
        JdbcTool jdbcTool = new JdbcTool();
        String sql = "insert into books(name,author,money,express,img) values(?,?,?,?,?)";


        try {
            conn = jdbcTool.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1, bookname);
            ps.setString(2, author);
            ps.setDouble(3, money);
            ps.setString(4, express);
            ps.setString(5, img);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcTool.release(conn, ps, null);
        }
    }

    /**
     * 删除图书
     *
     * @param id id
     */
    public void delete(Integer id) {
        JdbcTool jdbcTool = new JdbcTool();

        try {
            conn = jdbcTool.getConn();
            String sql = "delete from books where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcTool.release(conn, ps, null);
        }

    }

    /**
     * 根据id获取对应的img地址
     *
     * @param id id
     * @return 图片路径
     */
    public String getFilePath(Integer id) {
        JdbcTool jdbcTool = new JdbcTool();
        String sql = "select * from books where id=?";
        String img = "";
        try {
            conn = jdbcTool.getConn();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                img = rs.getString("img");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcTool.release(conn, ps, rs);
        }
        return img;
    }


    /**
     * 编辑图书
     *
     * @param name 书名
     * @param author 作者
     * @param express 出版社
     * @param money 价格
     * @param img 图片
     * @param id id
     */
    public void edit(String name, String author, String express, double money, String img, Integer id) {
        JdbcTool jdbcTool = new JdbcTool();

        try {
            conn = jdbcTool.getConn();
            String sql = "update books set name=?,author=?,express=?,money=?,img=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, author);
            ps.setString(3, express);
            ps.setDouble(4, money);
            ps.setString(5, img);
            ps.setInt(6, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcTool.release(conn, ps, null);
        }
    }

    /**
     * 查询图书
     *
     * @param id id
     * @return com.ihtry.entity.Book
     */
    public Book queryId(Integer id) {
        Book book = null;
        JdbcTool jdbcTool = new JdbcTool();

        try {
            conn = jdbcTool.getConn();
            String sql = "select * from books where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer id1 = rs.getInt("id");
                String name = rs.getString("name");
                String author = rs.getString("author");
                String express = rs.getString("express");
                double money = rs.getDouble("money");
                String img = rs.getString("img");
                book = new Book(id1, name, author, money, express, img);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return book;

    }


}
